using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Text;
using ECommerce.DAL;
using eShop.Model;


namespace eShop.DataAccess
{
    /// <summary>
    /// This object represents the Private Fields of TblProductCategory.
    /// </summary>

    [System.ComponentModel.DataObject]
    public class CategoryDAO : DatabaseUtility
    {
        #region TblProductCategory Static Methods
        /************************************************************************************
                                    -- CREATE METHOD (STATIC) --
        *************************************************************************************/
        /************************* Architecure note:**********************************
        Note 1:
        This method is specially useful for the case, where we need to use Object Data Source controls.
        For ObjectDataSource/SqlDataSource controls, the insert/update metnod requires paramters methods, rather property based methods.
        However, there is still a way to pass property based objects as method parameter for data source controls, 
        but in that case we could not use Sql type, as view controls or asp parameter control can only contain few of primitive type.
        The main reason to remain the Sql Type in our object architecture is it is faster and contains better way for null value support.
        
        Note 2: 
        Non-primary key: it should be primitive type, as the asp paramter control (included in data source control) supports only primitive types.
        /*****************************************************************************/
        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Insert, true)]
        public static Int32 CreateNewTblProductCategory
        (
            String categoryName,
            String categoryTitle,
            String categoryDescription,
            String categoryImageUrl,
            String categoryUrl,
            String displayOrder,
            Boolean isFeatured,
            Boolean isActive,
            Boolean isDeleted,
            DateTime createDate,
            Int32 subCategoryCount,
            Int32 productCount,
            Int32 parentCategoryId,
            String productDisplayOrder,
            String categoryBannerImageUrl
        )
        {
            //creating an empty TblProductCategory object
            Category tblProductCategory = new Category();
            tblProductCategory.CategoryName = categoryName;
            tblProductCategory.CategoryTitle = categoryTitle;
            tblProductCategory.CategoryDescription = categoryDescription;
            tblProductCategory.CategoryImageUrl = categoryImageUrl;
            tblProductCategory.CategoryUrl = categoryUrl;
            tblProductCategory.DisplayOrder = displayOrder;
            tblProductCategory.IsFeatured = isFeatured;
            tblProductCategory.IsActive = isActive;
            tblProductCategory.IsDeleted = isDeleted;
            tblProductCategory.CreateDate = createDate;
            tblProductCategory.SubCategoryCount = subCategoryCount;
            tblProductCategory.ProductCount = productCount;
            tblProductCategory.ParentCategoryId = parentCategoryId;
            tblProductCategory.ProductDisplayOrder = productDisplayOrder;
            tblProductCategory.CategoryBannerImageUrl = categoryBannerImageUrl;

            return CategoryDAO.CreateNewTblProductCategory(tblProductCategory);
        }

        /************************************************************************************
                 -- UPDATE METHODS (STATIC) --
        *************************************************************************************/

        /************************* Architecure note:**********************************
        Note 1:
        This method is specially useful for the case, where we need to use Object Data Source controls.
        For ObjectDataSource/SqlDataSource controls, the insert/update metnod requires paramters methods, rather property based methods.
        However, there is still a way to pass property based objects as method parameter for data source controls, 
        but in that case we could not use Sql type, as view controls or asp parameter control can only contain few of primitive type.
        The main reason to remain the Sql Type in our object architecture is it is faster and contains better way for null value support.
        
        Note 2: 
        There the two types of paramaters will be passed here.
         * a. Primary key paramater: it should be Sql type, as we will bind the primary key property for the view controls (form view, details view etc) as datakey.
         * b. Non-primary key: it should be primitive type, as the asp paramter control (included in data source control) supports only primitive types.
        /*****************************************************************************/

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Update, true)]
        public static bool UpdateTblProductCategory
        (
            Int32 categoryId,
            String categoryName,
            String categoryTitle,
            String categoryDescription,
            String categoryImageUrl,
            String categoryUrl,
            String displayOrder,
            Boolean isFeatured,
            Boolean isActive,
            Boolean isDeleted,
            DateTime createDate,
            Int32 subCategoryCount,
            Int32 productCount,
            Int32 parentCategoryId,
            String productDisplayOrder,
            String categoryBannerImageUrl
        )
        {
            Category tblProductCategory = CategoryDAO.GetTblProductCategoryByCategoryId(categoryId);

            tblProductCategory.CategoryName = categoryName;
            tblProductCategory.CategoryTitle = categoryTitle;
            tblProductCategory.CategoryDescription = categoryDescription;
            tblProductCategory.CategoryImageUrl = categoryImageUrl;
            tblProductCategory.CategoryUrl = categoryUrl;
            tblProductCategory.DisplayOrder = displayOrder;
            tblProductCategory.IsFeatured = isFeatured;
            tblProductCategory.IsActive = isActive;
            tblProductCategory.IsDeleted = isDeleted;
            tblProductCategory.CreateDate = createDate;
            tblProductCategory.SubCategoryCount = subCategoryCount;
            tblProductCategory.ProductCount = productCount;
            tblProductCategory.ParentCategoryId = parentCategoryId;
            tblProductCategory.ProductDisplayOrder = productDisplayOrder;
            tblProductCategory.CategoryBannerImageUrl = categoryBannerImageUrl;

            return CategoryDAO.UpdateTblProductCategory(tblProductCategory);
        }

        /************************************************************************************
                         -- DELETE METHODS --
        *************************************************************************************/
        /************************* Architecure note:**********************************
        We have used the employeeId parameter as SqlInt32, to bind the delete method with object data source and view control directly.
        ****************************************************************************/
        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Update, true)]
        public static bool DeleteTblProductCategory
        (
            Int32 categoryId
        )
        {
            //Validate Input
            if (categoryId <= GetCategoryIdMinValue)
                throw (new ArgumentOutOfRangeException("categoryId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTCATEGORY_DELETETBLPRODUCTCATEGORY);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? false : true);

        }

        /// <summary>
        /// Deletes a set of employees from the database.
        /// </summary>
        public static bool DeleteTblProductCategoryList(System.Collections.Generic.List<Int32> categoryIdListToDelete)
        {
            //Validate Input
            foreach (Int32 categoryId in categoryIdListToDelete)
                if (categoryId <= GetCategoryIdMinValue)
                    throw (new ArgumentOutOfRangeException("categoryId"));

            //performing deletion operation //

            string xmlString = FormatXMLForIdArray(categoryIdListToDelete);

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@CategoryIdList", SqlDbType.Xml, xmlString.ToString().Length, ParameterDirection.Input, xmlString.ToString());
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTCATEGORY_DELETETBLPRODUCTCATEGORYLIST);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == categoryIdListToDelete.Count ? true : false);
        }

        /************************************************************************************
                                 -- GET BY PRIMARY METHODS --
        *************************************************************************************/
        /************************* Architecure note:**********************************
        Retrives an Employee from database, based on it's primary key.
        To view or update an employee, everytime we must have to use this type of 'get' method, 
        since we don't have direct access to the Primary key value for assigning value.
        We have used the employeeId parameter as SqlInt32, to bind the delete method with object data source and view control directly.
        /*****************************************************************************/
        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
        public static Category GetTblProductCategoryByCategoryId(Int32 categoryId)
        {
            if (categoryId <= GetCategoryIdMinValue)
                throw (new ArgumentOutOfRangeException("categoryId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTCATEGORY_GETTBLPRODUCTCATEGORYBYCATEGORYID);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            CustomCollection<Category> objCollection = ((CustomCollection<Category>)ExecuteReaderCmd(sqlCmd, test));

            if (objCollection.Count > 0)
                return objCollection[0];
            else
                return null;
        }

        /************************************************************************************
                         -- GET BY FOREIGN KEY METHODS --
        *************************************************************************************/

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)]
        public static CustomCollection<Category> GetTblProductCategoryByParentCategoryId(Int32 parentCategoryId)
        {
            if (parentCategoryId <= GetParentCategoryIdMinValue)
                throw (new ArgumentOutOfRangeException("parentCategoryId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ParentCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, parentCategoryId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTCATEGORY_GETTBLPRODUCTCATEGORYBYPARENTCATEGORYID);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            CustomCollection<Category> objCollection = ((CustomCollection<Category>)ExecuteReaderCmd(sqlCmd, test));

            if (objCollection.Count > 0)
                return objCollection;
            else
                return null;
        }

        /// <summary>
        /// Gets all the TblProductCategory regarding 'PREFERRED_LANGUAGE_ID' foreign key.
        /// It supports efficient paging.
        /// </summary>
        /// <returns>A collection of TblProductCategory.</returns>
        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)]
        public static CustomCollection<Category> GetTblProductCategoryByParentCategoryIdPaged(Int32 parentCategoryId, String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            //Validate Input
            if (parentCategoryId <= GetParentCategoryIdMinValue)
                throw (new ArgumentOutOfRangeException("parentCategoryId"));

            if (orderBy == "")
                orderBy = "ParentCategoryId";

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ParentCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, parentCategoryId);
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRowIndex);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, maximumRows);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTCATEGORY_GETTBLPRODUCTCATEGORYBYPARENTCATEGORYIDPAGED);

            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            CustomCollection<Category> objCollection = ((CustomCollection<Category>)ExecuteReaderCmd(sqlCmd, test));

            return objCollection;
        }

        /// <summary>
        /// This is a supporting method of 'GetEmployeeByPREFERRED_LANGUAGE_IDPagedPagedCountPaged' method.
        /// Used in paging, which returns the total number of records, returned from the ''GetEmployeeByReportsToPaged'' method (regardless of page size).
        /// Although we are not using the parameters, but we need the same signature as ''GetEmployeeByReportsToPaged'' to support paging.
        /// </summary>
        public static int GetTblProductCategoryByParentCategoryIdPagedCount(Int32 parentCategoryId, String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            //Validate Input
            if (parentCategoryId <= GetParentCategoryIdMinValue)
                throw (new ArgumentOutOfRangeException("parentCategoryId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ParentCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, parentCategoryId);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTCATEGORY_GETTBLPRODUCTCATEGORYBYPARENTCATEGORYIDPAGEDCOUNT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        /************************************************************************************
                 -- GET ALL METHODS --
        *************************************************************************************/
        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
        public static CustomCollection<Category> GetAllTblProductCategory()
        {

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTCATEGORY_GETALLTBLPRODUCTCATEGORY);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            CustomCollection<Category> objCollection = ((CustomCollection<Category>)ExecuteReaderCmd(sqlCmd, test));

            return objCollection;
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
        public static CustomCollection<Category> GetAllTblProductCategoryPaged(String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            //Validate Input
            if (orderBy == "")
                orderBy = "CategoryId";

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRowIndex);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, maximumRows);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTCATEGORY_GETALLTBLPRODUCTCATEGORYPAGED);

            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            CustomCollection<Category> objCollection = ((CustomCollection<Category>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]
        public static int GetAllTblProductCategoryPagedCount(String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTCATEGORY_GETALLTBLPRODUCTCATEGORYPAGEDCOUNT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        #endregion
        #region Employee Instance Methods

        public static CustomCollection<Category> GetProductCategoryByCondition(int howMany, bool isActive, bool isDeleted, bool isFeatured)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@HowMany", SqlDbType.Int, 40, ParameterDirection.Input, howMany);
            AddParameterToSqlCmd(sqlCmd, "@IsActive", SqlDbType.Bit, 1, ParameterDirection.Input, isActive);
            AddParameterToSqlCmd(sqlCmd, "@IsDeleted", SqlDbType.Bit, 1, ParameterDirection.Input, isDeleted);
            AddParameterToSqlCmd(sqlCmd, "@IsFeatured", SqlDbType.Bit, 1, ParameterDirection.Input, isFeatured);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_TBL_PRODUCT_CATEGORY_BY_CONDITION);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            CustomCollection<Category> objCollection = ((CustomCollection<Category>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static CustomCollection<Category> GetActiveSubCategoriesByParentCategory(int parentCategoryId)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ParentCategoryId", SqlDbType.Int, 40, ParameterDirection.Input, parentCategoryId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_ACTIVE_SUBCATEGORIES_BY_PARENTCATEGORYID);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            CustomCollection<Category> objCollection = ((CustomCollection<Category>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static CustomCollection<Category> GetActiveSubCategoriesByParentCategoryIdPaged(int parentCategoryId, string orderBy, int startRow, int pageSize)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ParentCategoryId", SqlDbType.Int, 40, ParameterDirection.Input, parentCategoryId);
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 40, ParameterDirection.Input, startRow);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 40, ParameterDirection.Input, pageSize);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_ACTIVE_SUBCATEGORIES_BY_PARENTCATEGORYID_PAGED);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            CustomCollection<Category> objCollection = ((CustomCollection<Category>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static int GetActiveSubCategoriesByParentCategoryIdPagedCount(int parentCategoryId)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ParentCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, parentCategoryId);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_ACTIVE_SUBCATEGORIES_BY_PARENTCATEGORYID_PAGED_COUNT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static CustomCollection<Category> GetActiveCategoriesByCount(int howMany)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@howMany", SqlDbType.Int, 0, ParameterDirection.Input, howMany);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_ACTIVE_CATEGORIES_BY_COUNT);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            CustomCollection<Category> objCollection = ((CustomCollection<Category>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }
        public static CustomCollection<Category> GetActiveRootCategories()
        {
            SqlCommand sqlCmd = new SqlCommand();
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_ACTIVE_ROOT_CATEGORIES);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            CustomCollection<Category> objCollection = ((CustomCollection<Category>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static int GetProductCategoryCountByName(string CategoryName)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryName", SqlDbType.NVarChar, 500, ParameterDirection.Input, CategoryName);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_CATEGORY_COUNT_BY_NAME);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static int GetProductCategoryCountByNameAndParent(int parentCatgoryId, string CategoryName)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ParentCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, parentCatgoryId);
            AddParameterToSqlCmd(sqlCmd, "@CategoryName", SqlDbType.NVarChar, 500, ParameterDirection.Input, CategoryName);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_CATEGORY_COUNT_BY_NAME_AND_PARENT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static CustomCollection<Category> GetProductCategoryByPrefix(string prefix)
        {
            if (string.IsNullOrEmpty(prefix))
                throw (new ArgumentOutOfRangeException("prefix"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@Prefix", SqlDbType.NVarChar, 50, ParameterDirection.Input, prefix);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_ACTIVE_CATEGORIES_BY_PREFIX);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            CustomCollection<Category> objCollection = ((CustomCollection<Category>)ExecuteReaderCmd(sqlCmd, reader));

            return objCollection;
        }

        public static CustomCollection<Category> GetNonDeletedSubCategoriesByParentCategoryIdPaged(int parentId, string orderBy, int startRow, int pageSize)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ParentCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, parentId);
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRow);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_SUBCATEGORIES_BY_PARENTCATEGORYID_PAGED);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            CustomCollection<Category> objCollection = ((CustomCollection<Category>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static int GetNonDeletedSubCategoriesByParentCategoryIdCount(int parentId)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ParentCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, parentId);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_SUBCATEGORIES_BY_PARENTCATEGORYID_COUNT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static CustomCollection<Category> GetNonDeletedSubCategoriesPaged(string orderBy, int startRow, int pageSize)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRow);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_CATEGORIES_PAGED);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            CustomCollection<Category> objCollection = ((CustomCollection<Category>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static int GetNonDeletedCategoriesCount()
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_CATEGORIES_COUNT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static CustomCollection<Category> GetAllCategoriesByNamePaged(string categoryName, string orderBy, int startRow, int pageSize)
        {
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@categoryName", SqlDbType.VarChar, 500, ParameterDirection.Input, categoryName);
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRow);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_ALL_CATEGORIES_BY_NAME_PAGED);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            CustomCollection<Category> objCollection = ((CustomCollection<Category>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static int GetAllCategoriesByNameCount(string categoryName)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryName", SqlDbType.NVarChar, 500, ParameterDirection.Input, categoryName);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_ALL_CATEGORIES_BY_NAME_COUNT);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static CustomCollection<Category> GetNonDeletedCategoriesByNamePaged(string categoryName, string orderBy, int startRow, int pageSize)
        {
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@categoryName", SqlDbType.VarChar, 500, ParameterDirection.Input, categoryName);
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRow);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_CATEGORIES_BY_NAME_PAGED);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            CustomCollection<Category> objCollection = ((CustomCollection<Category>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static int GetNonDeletedCategoriesByNameCount(string categoryName)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryName", SqlDbType.NVarChar, 500, ParameterDirection.Input, categoryName);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_CATEGORIES_BY_NAME_COUNT);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }
        public static CustomCollection<Category> GetAllCategoriesByParentCategoryIdAndNamePaged(int categoryId, string categoryName, string orderBy, int startRow, int pageSize)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ParentcategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            AddParameterToSqlCmd(sqlCmd, "@categoryName", SqlDbType.VarChar, 500, ParameterDirection.Input, categoryName);
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRow);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_ALL_CATEGORIES_BY_PARENTCATEGORYID_NAME_PAGED);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            CustomCollection<Category> objCollection = ((CustomCollection<Category>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static int GetAllCategoriesByParentCategoryIdAndNameCount(int parentCategoryId, string categoryName)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryName", SqlDbType.NVarChar, 500, ParameterDirection.Input, categoryName);
            AddParameterToSqlCmd(sqlCmd, "@parentCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, parentCategoryId);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_ALL_CATEGORIES_BY_PARENTCATEGORYID_NAME_COUNT);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static CustomCollection<Category> GetNonDeletedCategoriesByParentCategoryIdAndNamePaged(int categoryId, string categoryName, string orderBy, int startRow, int pageSize)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ParentcategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            AddParameterToSqlCmd(sqlCmd, "@categoryName", SqlDbType.VarChar, 500, ParameterDirection.Input, categoryName);
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRow);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, pageSize);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_CATEGORIES_BY_PARENTCATEGORYID_NAME_PAGED);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            CustomCollection<Category> objCollection = ((CustomCollection<Category>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static int GetNonDeletedCategoriesByParentCategoryIdAndNameCount(int parentCategoryId, string categoryName)
        {
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryName", SqlDbType.NVarChar, 500, ParameterDirection.Input, categoryName);
            AddParameterToSqlCmd(sqlCmd, "@ParentCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, parentCategoryId);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_CATEGORIES_BY_PARENTCATEGORYID_NAME_COUNT);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static void IncreaseSubCategoryCount(int categoryId)
        {
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_INCREASE_SUB_CATEGORY_COUNT);
            ExecuteScalarCmd(sqlCmd);
        }

        public static void DecreaseSubCategoryCount(int categoryId)
        {
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_DECREASE_SUB_CATEGORY_COUNT);
            ExecuteScalarCmd(sqlCmd);
        }

        public static Category GetTblProductCategoryByName(string category)
        {
            if (string.IsNullOrEmpty(category))
                throw (new ArgumentOutOfRangeException("category"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryName", SqlDbType.NVarChar, 500, ParameterDirection.Input, category);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_ACTIVE_CATEGORIES_BY_NAME);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            CustomCollection<Category> objCollection = ((CustomCollection<Category>)ExecuteReaderCmd(sqlCmd, reader));

            if (objCollection.Count > 0)
                return objCollection[0];
            else
                return null;
        }

        public static Category GetTblProductCategoryByUrl(string url)
        {
            url = "~/" + url;
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@CategoryURL", SqlDbType.NVarChar, 500, ParameterDirection.Input, url);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, "spr_TblProductCategory_GetTblProductCategoryByUrl");
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            CustomCollection<Category> objCollection = ((CustomCollection<Category>)ExecuteReaderCmd(sqlCmd, reader));

            if (objCollection.Count > 0)
                return objCollection[0];
            else
                return null;
        }


        public static Category GetTblProductCategoryByName(int parentCategory, string subCategory)
        {
            if (string.IsNullOrEmpty(subCategory))
                throw (new ArgumentOutOfRangeException("subCategory"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ParentCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, parentCategory);
            AddParameterToSqlCmd(sqlCmd, "@CategoryName", SqlDbType.NVarChar, 500, ParameterDirection.Input, subCategory);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_GET_ACTIVE_CATEGORIES_BY_PARENT_AND_SUBCATEGORY_NAME);
            GenerateCollectionFromReader reader = new GenerateCollectionFromReader(GenerateTblProductCategoryCollectionFromReader);
            CustomCollection<Category> objCollection = ((CustomCollection<Category>)ExecuteReaderCmd(sqlCmd, reader));

            if (objCollection.Count > 0)
                return objCollection[0];
            else
                return null;
        }

        public static int UpdateTblProductCategoryUrl(string oldCategoryUrl, string newCategoryUrl)
        {
            if (string.IsNullOrEmpty(oldCategoryUrl) || string.IsNullOrEmpty(newCategoryUrl))
                throw (new ArgumentOutOfRangeException("UpdateTblProductCategoryUrl"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@OldCategoryUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, oldCategoryUrl);
            AddParameterToSqlCmd(sqlCmd, "@NewCategoryUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, newCategoryUrl);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBL_PRODUCTCATEGORY_UPDATECATEGORYURL);
            ExecuteScalarCmd(sqlCmd);
            return (Int32.Parse(sqlCmd.Parameters["@ReturnValue"].Value.ToString()));
        }
        /************************************************************************************
                                            -- SAVE (INSERT/UPDATE) METHODS (INSTANCE) --
                *************************************************************************************/
        /// <summary>
        /// Saves (inserts and updates) an Employee into database.
        /// The insertion occur if the EmployeeId conatins default value.
        /// Otherwises it updates the Employee into the database, based on the primary key value stored in EmployeeId.
        /// This technique ensures an Employee will not be updated without having a valid EmployeeId.
        /// </summary>
        public static bool Save(Category category)
        {
            if (category.CategoryId <= GetCategoryIdMinValue)
            {
                Int32 TempId = CreateNewTblProductCategory(category);
                if (TempId > 0)
                {
                    category.CategoryId = TempId;
                    return true;
                }
                else
                {
                    // error occurs
                    return false;
                }
            }
            else
            {	//This is a update Command
                return (UpdateTblProductCategory(category));
            }

        }

        public static Int32 CreateNewTblProductCategory(Category newTblProductCategory)
        {
            // validate Parameters
            if (newTblProductCategory == null)
                throw (new ArgumentNullException("newTblProductCategory"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            int length = 0;
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParameterToSqlCmd(sqlCmd, "@CategoryName", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProductCategory.CategoryName);
            AddParameterToSqlCmd(sqlCmd, "@CategoryTitle", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProductCategory.CategoryTitle);
            AddParameterToSqlCmd(sqlCmd, "@CategoryDescription", SqlDbType.NVarChar, 4000, ParameterDirection.Input, newTblProductCategory.CategoryDescription);
            AddParameterToSqlCmd(sqlCmd, "@CategoryImageUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProductCategory.CategoryImageUrl);
            AddParameterToSqlCmd(sqlCmd, "@CategoryUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProductCategory.CategoryUrl);
            AddParameterToSqlCmd(sqlCmd, "@DisplayOrder", SqlDbType.NVarChar, 50, ParameterDirection.Input, newTblProductCategory.DisplayOrder);
            AddParameterToSqlCmd(sqlCmd, "@IsFeatured", SqlDbType.Bit, 0, ParameterDirection.Input, newTblProductCategory.IsFeatured);
            AddParameterToSqlCmd(sqlCmd, "@IsActive", SqlDbType.Bit, 0, ParameterDirection.Input, newTblProductCategory.IsActive);
            AddParameterToSqlCmd(sqlCmd, "@IsDeleted", SqlDbType.Bit, 0, ParameterDirection.Input, newTblProductCategory.IsDeleted);
            AddParameterToSqlCmd(sqlCmd, "@CreateDate", SqlDbType.DateTime, 0, ParameterDirection.Input, newTblProductCategory.CreateDate);
            AddParameterToSqlCmd(sqlCmd, "@SubCategoryCount", SqlDbType.Int, 0, ParameterDirection.Input, newTblProductCategory.SubCategoryCount);
            AddParameterToSqlCmd(sqlCmd, "@ProductCount", SqlDbType.Int, 0, ParameterDirection.Input, newTblProductCategory.ProductCount);
            AddParameterToSqlCmd(sqlCmd, "@ParentCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, newTblProductCategory.ParentCategoryId);
            AddParameterToSqlCmd(sqlCmd, "@ProductDisplayOrder", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProductCategory.ProductDisplayOrder);
            AddParameterToSqlCmd(sqlCmd, "@CategoryBannerImageUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProductCategory.CategoryBannerImageUrl);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTCATEGORY_CREATENEWTBLPRODUCTCATEGORY);
            ExecuteScalarCmd(sqlCmd);
            return (Int32.Parse(sqlCmd.Parameters["@ReturnValue"].Value.ToString()));

        }
        /// <summary>
        /// Updates an TblProductCategory instance to the database.
        /// </summary>
        public static bool UpdateTblProductCategory(Category newTblProductCategory)
        {
            // validate Parameters
            if (newTblProductCategory == null)
                throw (new ArgumentNullException("newTblProductCategory"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            int length = 0;
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParameterToSqlCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, newTblProductCategory.CategoryId);
            AddParameterToSqlCmd(sqlCmd, "@CategoryName", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProductCategory.CategoryName);
            AddParameterToSqlCmd(sqlCmd, "@CategoryTitle", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProductCategory.CategoryTitle);
            AddParameterToSqlCmd(sqlCmd, "@CategoryDescription", SqlDbType.NVarChar, 4000, ParameterDirection.Input, newTblProductCategory.CategoryDescription);
            AddParameterToSqlCmd(sqlCmd, "@CategoryImageUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProductCategory.CategoryImageUrl);
            AddParameterToSqlCmd(sqlCmd, "@CategoryUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProductCategory.CategoryUrl);
            AddParameterToSqlCmd(sqlCmd, "@DisplayOrder", SqlDbType.NVarChar, 50, ParameterDirection.Input, newTblProductCategory.DisplayOrder);
            AddParameterToSqlCmd(sqlCmd, "@IsFeatured", SqlDbType.Bit, 0, ParameterDirection.Input, newTblProductCategory.IsFeatured);
            AddParameterToSqlCmd(sqlCmd, "@IsActive", SqlDbType.Bit, 0, ParameterDirection.Input, newTblProductCategory.IsActive);
            AddParameterToSqlCmd(sqlCmd, "@IsDeleted", SqlDbType.Bit, 0, ParameterDirection.Input, newTblProductCategory.IsDeleted);
            AddParameterToSqlCmd(sqlCmd, "@CreateDate", SqlDbType.DateTime, 0, ParameterDirection.Input, newTblProductCategory.CreateDate);
            AddParameterToSqlCmd(sqlCmd, "@SubCategoryCount", SqlDbType.Int, 0, ParameterDirection.Input, newTblProductCategory.SubCategoryCount);
            AddParameterToSqlCmd(sqlCmd, "@ProductCount", SqlDbType.Int, 0, ParameterDirection.Input, newTblProductCategory.ProductCount);
            AddParameterToSqlCmd(sqlCmd, "@ParentCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, newTblProductCategory.ParentCategoryId);
            AddParameterToSqlCmd(sqlCmd, "@ProductDisplayOrder", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProductCategory.ProductDisplayOrder);
            AddParameterToSqlCmd(sqlCmd, "@CategoryBannerImageUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProductCategory.CategoryBannerImageUrl);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TBLPRODUCTCATEGORY_UPDATETBLPRODUCTCATEGORY);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? false : true);
        }
        #endregion

        #region TblProductCategory Related Collections
        /// <summary>
        /// Creates and returns a strongly typed collection of Employee custom entity. 
        /// The colelction is created through iterating on the IdataReader object which contains Employee information, as a set of records, similar to tabular format.
        /// </summary>
        public static CollectionBase GenerateTblProductCategoryCollectionFromReader(IDataReader returnData)
        {

            //creating the instance of Employee collection
            CustomCollection<Category> colTblProductCategory = new CustomCollection<Category>();

            /************************* Architecure note:**********************************
             * Below code includes the null value functionality to retieve the data which has nill value in database end.
             * Design consideration:
             * Besides general data fields, special care should be taken for primary keys, to assign '0'/defaultvalue, rather passing 'Null' value in constructor parameter.
             * Although we are considering sqldb tyoe data for the current data container, but while retireving data from database end, through datareader object, we need to cast data reader objects using .net primitive data type, 
             * rather using sqldb type to cast, since data reader objects don't support sql db type to be casted.
             *****************************************************************************/

            //Iterating thriugh the data reader, to generate Employee collection.

            //getting the Employee collection by iterating thru the data reader
            while (returnData.Read())
            {
                //passing the TblProductCategory constructor parameters from the current instance of data reader fields.
                Category newTblProductCategory = new Category()
                {
                    CategoryId = returnData["CategoryId"] == System.DBNull.Value ? GetCategoryIdMinValue : (Int32)returnData["CategoryId"],
                    CategoryName = returnData["CategoryName"] == System.DBNull.Value ? string.Empty : (String)returnData["CategoryName"],
                    CategoryTitle = returnData["CategoryTitle"] == System.DBNull.Value ? string.Empty : (String)returnData["CategoryTitle"],
                    CategoryDescription = returnData["CategoryDescription"] == System.DBNull.Value ? string.Empty : (String)returnData["CategoryDescription"],
                    CategoryImageUrl = returnData["CategoryImageUrl"] == System.DBNull.Value ? string.Empty : (String)returnData["CategoryImageUrl"],
                    CategoryUrl = returnData["CategoryUrl"] == System.DBNull.Value ? string.Empty : (String)returnData["CategoryUrl"],
                    DisplayOrder = returnData["DisplayOrder"] == System.DBNull.Value ? string.Empty : (String)returnData["DisplayOrder"],
                    IsFeatured = returnData["IsFeatured"] == System.DBNull.Value ? false : (Boolean)returnData["IsFeatured"],
                    IsActive = returnData["IsActive"] == System.DBNull.Value ? false : (Boolean)returnData["IsActive"],
                    IsDeleted = returnData["IsDeleted"] == System.DBNull.Value ? false : (Boolean)returnData["IsDeleted"],
                    CreateDate = returnData["CreateDate"] == System.DBNull.Value ? DateTime.MinValue : (DateTime)returnData["CreateDate"],
                    SubCategoryCount = returnData["SubCategoryCount"] == System.DBNull.Value ? 0 : (Int32)returnData["SubCategoryCount"],
                    ProductCount = returnData["ProductCount"] == System.DBNull.Value ? 0 : (Int32)returnData["ProductCount"],
                    ParentCategoryId = returnData["ParentCategoryId"] == System.DBNull.Value ? 0 : (Int32)returnData["ParentCategoryId"],
                    ProductDisplayOrder = returnData["ProductDisplayOrder"] == System.DBNull.Value ? string.Empty : (String)returnData["ProductDisplayOrder"],
                    CategoryBannerImageUrl = returnData["CategoryBannerImageUrl"] == System.DBNull.Value ? string.Empty : (String)returnData["CategoryBannerImageUrl"]
                };
                //adding the Employee to the collection
                colTblProductCategory.Add(newTblProductCategory);
            }

            //returns the collection of Companys 
            return (colTblProductCategory);
        }

        #endregion
        #region Constants and Default Values

        public static Int32 GetCategoryIdMinValue
        {
            get
            {
                return 0;
            }
        }
        public static Int32 GetParentCategoryIdMinValue
        {
            get
            {
                return 0;
            }
        }

        //create method related contants
        internal const string SP_TBLPRODUCTCATEGORY_CREATENEWTBLPRODUCTCATEGORY = "sp_TblProductCategory_CreateNewTblProductCategory";
        internal const string SP_TBLPRODUCTCATEGORY_UPDATETBLPRODUCTCATEGORY = "sp_TblProductCategory_UpdateTblProductCategory";
        internal const string SP_TBLPRODUCTCATEGORY_DELETETBLPRODUCTCATEGORY = "sp_TblProductCategory_DeleteTblProductCategory";
        internal const string SP_TBLPRODUCTCATEGORY_DELETETBLPRODUCTCATEGORYLIST = "sp_TblProductCategory_DeleteTblProductCategoryList";
        internal const string SP_TBLPRODUCTCATEGORY_GETTBLPRODUCTCATEGORYBYCATEGORYID = "sp_TblProductCategory_GetTblProductCategoryByCategoryId";
        internal const string SP_TBLPRODUCTCATEGORY_GETTBLPRODUCTCATEGORYBYPARENTCATEGORYID = "sp_TblProductCategory_GetTblProductCategoryByParentCategoryId";
        internal const string SP_TBLPRODUCTCATEGORY_GETTBLPRODUCTCATEGORYBYPARENTCATEGORYIDPAGED = "sp_TblProductCategory_GetTblProductCategoryByParentCategoryIdPaged";
        internal const string SP_TBLPRODUCTCATEGORY_GETTBLPRODUCTCATEGORYBYPARENTCATEGORYIDPAGEDCOUNT = "sp_TblProductCategory_GetTblProductCategoryByParentCategoryIdPagedCount";
        internal const string SP_TBLPRODUCTCATEGORY_GETALLTBLPRODUCTCATEGORY = "sp_TblProductCategory_GetAllTblProductCategory";
        internal const string SP_TBLPRODUCTCATEGORY_GETALLTBLPRODUCTCATEGORYPAGED = "sp_TblProductCategory_GetAllTblProductCategoryPaged";
        internal const string SP_TBLPRODUCTCATEGORY_GETALLTBLPRODUCTCATEGORYPAGEDCOUNT = "sp_TblProductCategory_GetAllTblProductCategoryPagedCount";

        private const string SP_TBL_PRODUCTCATEGORY_GET_ACTIVE_CATEGORIES_BY_PARENT_AND_SUBCATEGORY_NAME = "sp_TblProductCategory_Get_ProductCategory_By_Parent_And_SubCategory_Name";
        private const string SP_TBL_PRODUCTCATEGORY_GET_ACTIVE_CATEGORIES_BY_NAME = "sp_TblProductCategory_Get_ProductCategory_By_Name";
        private const string SP_TBL_PRODUCTCATEGORY_GET_TBL_PRODUCT_CATEGORY_BY_CONDITION = "sp_TblProductCategory_Get_ProductCategory_By_Condition";
        private const string SP_TBL_PRODUCTCATEGORY_GET_ACTIVE_SUBCATEGORIES_BY_PARENTCATEGORYID = "sp_TblProductCategory_Get_Active_SubCategories_By_ParentCategoryId";
        private const string SP_TBL_PRODUCTCATEGORY_GET_ACTIVE_SUBCATEGORIES_BY_PARENTCATEGORYID_PAGED = "sp_TblProductCategory_Get_Active_SubCategories_By_ParentCategoryIdPaged";
        private const string SP_TBL_PRODUCTCATEGORY_GET_ACTIVE_SUBCATEGORIES_BY_PARENTCATEGORYID_PAGED_COUNT = "sp_TblProductCategory_Get_Active_SubCategories_By_ParentCategoryIdPagedCount";
        private const string SP_TBL_PRODUCTCATEGORY_GET_ACTIVE_CATEGORIES_BY_COUNT = "sp_TblProductCategory_Get_Active_Categories_By_Count";
        private const string SP_TBL_PRODUCTCATEGORY_GET_ACTIVE_ROOT_CATEGORIES = "sp_TblProductCategory_Get_Active_RootCategories";
        private const string SP_TBL_PRODUCTCATEGORY_GET_CATEGORY_COUNT_BY_NAME = "sp_TblProductCategory_Get_Category_Count_By_Name";
        private const string SP_TBL_PRODUCTCATEGORY_GET_CATEGORY_COUNT_BY_NAME_AND_PARENT = "sp_TblProductCategory_Get_Category_Count_By_Name_And_ParentId";
        private const string SP_TBL_PRODUCTCATEGORY_GET_ACTIVE_CATEGORIES_BY_PREFIX = "sp_TblProductCategory_Get_Active_Categories_By_Prefix";
        private const string SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_SUBCATEGORIES_BY_PARENTCATEGORYID_PAGED = "sp_TblProductCategory_Get_NonDeleted_SubCategories_By_ParentCategoryIdPaged";
        private const string SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_SUBCATEGORIES_BY_PARENTCATEGORYID_COUNT = "sp_TblProductCategory_Get_NonDeleted_SubCategories_By_ParentCategoryIdCount";
        private const string SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_CATEGORIES_PAGED = "sp_TblProductCategory_Get_NonDeleted_SubCategories_Paged";
        private const string SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_CATEGORIES_COUNT = "sp_TblProductCategory_Get_NonDeleted_SubCategories_Count";
        private const string SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_CATEGORIES_BY_NAME_PAGED = "sp_TblProductCategory_Get_NonDeleted_Categories_By_Name_Paged";
        private const string SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_CATEGORIES_BY_NAME_COUNT = "sp_TblProductCategory_Get_NonDeleted_Categories_By_Name_Count";
        private const string SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_CATEGORIES_BY_PARENTCATEGORYID_NAME_PAGED = "sp_TblProductCategory_Get_NonDeleted_Categories_By_ParentCategoryId_Name_Paged";
        private const string SP_TBL_PRODUCTCATEGORY_GET_NONDELETED_CATEGORIES_BY_PARENTCATEGORYID_NAME_COUNT = "sp_TblProductCategory_Get_NonDeleted_Categories_By_ParentCategoryId_Name_Count";
        private const string SP_TBL_PRODUCTCATEGORY_GET_ALL_CATEGORIES_BY_NAME_PAGED = "sp_TblProductCategory_Get_All_Categories_By_Name_Paged";
        private const string SP_TBL_PRODUCTCATEGORY_GET_ALL_CATEGORIES_BY_NAME_COUNT = "sp_TblProductCategory_Get_All_Categories_By_Name_Count";
        private const string SP_TBL_PRODUCTCATEGORY_GET_ALL_CATEGORIES_BY_PARENTCATEGORYID_NAME_PAGED = "sp_TblProductCategory_Get_All_Categories_By_ParentCategoryId_Name_Paged";
        private const string SP_TBL_PRODUCTCATEGORY_GET_ALL_CATEGORIES_BY_PARENTCATEGORYID_NAME_COUNT = "sp_TblProductCategory_Get_All_Categories_By_ParentCategoryId_Name_Count";
        private const string SP_TBL_PRODUCTCATEGORY_INCREASE_SUB_CATEGORY_COUNT = "sp_TblProductCategory_Increase_Sub_Category_Count";
        private const string SP_TBL_PRODUCTCATEGORY_DECREASE_SUB_CATEGORY_COUNT = "sp_TblProductCategory_Decrease_Sub_Category_Count";
        private const string SP_TBL_PRODUCTCATEGORY_UPDATECATEGORYURL = "sp_TblProductCategory_UpdateCategoryUrl";

        #endregion
    }
}
